22
Source Code:
-- Create the student100 table
CREATE TABLE student100 (
regno NUMBER(10) PRIMARY KEY,
name VARCHAR(20),
mark1 NUMBER(5),
mark2 NUMBER(5),
total NUMBER(5),
avg NUMBER(4,2)
);
-- Insert values into the student100 table
INSERT INTO student100 VALUES (200, 'Andrew', 75, 89, 164, 82.00);
INSERT INTO student100 VALUES (201, 'Vel', 92, 87, 179, 89.50);
INSERT INTO student100 VALUES (202, 'Vasu', 79, 80, 159, 79.50);
INSERT INTO student100 VALUES (203, 'Priya', 75, 70, 145, 72.50);
INSERT INTO student100 VALUES (204, 'Jeeva', 70, 67, 137, 68.50);
INSERT INTO student100 VALUES (205, 'Simbu', 73, 71, 144, 72.00);
-- Select all records from the student100 table
SELECT * FROM student100;
-- Enable output for PL/SQL block
SET SERVEROUTPUT ON;
-- PL/SQL block to fetch details for a specific student
DECLARE
mark student100%ROWTYPE;
CURSOR details IS SELECT * FROM student100 WHERE regno = 203;
BEGIN
OPEN details;
DBMS_OUTPUT.PUT_LINE('Student details');
LOOP
FETCH details INTO mark;
EXIT WHEN details%NOTFOUND; -- Exit loop if no more rows are found
DBMS_OUTPUT.PUT_LINE('Student name: ' || mark.name);
DBMS_OUTPUT.PUT_LINE('Mark1: ' || mark.mark1);
DBMS_OUTPUT.PUT_LINE('Mark2: ' || mark.mark2);
DBMS_OUTPUT.PUT_LINE('Total: ' || mark.total);
DBMS_OUTPUT.PUT_LINE('Average: ' || mark.avg);
END LOOP;
CLOSE details;
DBMS_OUTPUT.PUT_LINE('Student details displayed');
END;
/
-- PL/SQL block to fetch and display all students from student100
DECLARE
s_regno student100.regno%TYPE;
s_name student100.name%TYPE;
s_mark1 student100.mark1%TYPE;